package com.yc.damai.util;

import java.io.IOException;
import java.io.InputStream;
// java 反射包 java.lang.reflect
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import com.yc.damai.bean.Category;

public class DBHelper {

	static {
		try {

			// 读入配置文件 Properties Map集合 ==》 读入配置文件
			Properties props = new Properties();
			// 定义输入流 ClassLoader 类加载器
			InputStream in = DBHelper.class.getClassLoader()
					// conn.properties 必须在项目根目录
					.getResourceAsStream("conn.properties");
			props.load(in);
			url = props.getProperty("url");
			user = props.getProperty("user");
			pwd = props.getProperty("pwd");
			String driver = props.getProperty("driver");
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			throw new RuntimeException("数据库驱动加载失败", e);
		} catch (IOException e) {
			throw new RuntimeException("配置文件读入失败！", e);
		}
	}

	private static String url;
	private static String user;
	private static String pwd;

	public static Connection openConnection() throws SQLException {
		Connection conn = DriverManager.getConnection(url, user, pwd);
		return conn;
	}
	
	public static List<Map<String, Object>> query(String sql, Object... params) {
		System.out.println("SQL: " + sql);
		System.out.println("参数: " + java.util.Arrays.toString(params));
		Connection conn = null;
		try {
			conn = openConnection();
			// 获取连接
			PreparedStatement ps = conn.prepareStatement(sql);
			// 设置参数
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i + 1, params[i]);
			}
			ResultSet rs = ps.executeQuery();
			// 获取结果集元数据对象
			ResultSetMetaData rsmd = rs.getMetaData();
			// 创建返回结果对象
			List<Map<String, Object>> ret = new ArrayList<>();
			while (rs.next()) {
				// 创建 map 集合
				Map<String, Object> row = new HashMap<String, Object>();
				// 获取每一个字段值, 设置到一个map中
				for (int i = 0; i < rsmd.getColumnCount(); i++) {
					String columnName = rsmd.getColumnName(i + 1);
					Object columnValue = rs.getObject(columnName);
					row.put(columnName, columnValue);
				}
				// 将 map 添加到 ret 中
				ret.add(row);
			}
			return ret;
		} catch (SQLException e) {
			throw new RuntimeException("执行SQL语句失败!", e);
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				throw new RuntimeException("连接关闭失败", e);
			}
		}
	}

	/**
	 * 定义泛型方法
	 */
	public static <T> List<T> query(String sql, Class<T> cls, Object... params) {
		System.out.println("SQL："+ sql);
		System.out.println("参数：" + java.util.Arrays.toString(params));
		Connection conn = null;
		try {
			conn = openConnection();
			// 获取连接
			PreparedStatement ps = conn.prepareStatement(sql);
			// 设置参数
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i + 1, params[i]);
			}
			// 执行语句
			ResultSet rs = ps.executeQuery();
			List<T> ret = new ArrayList<T>();
			// 元数据对象
			ResultSetMetaData rsmd = rs.getMetaData();
			for (; rs.next();) {
				// Java 黑科技 —— 反射
				T t = cls.newInstance(); // 创建对象 ==》 new XXX(); JavaBean
				// 遍历所有的的字段
				for (int i = 0; i < rsmd.getColumnCount(); i++) {
					// 获取字段名
					String columnName = rsmd.getColumnName(i+1);
					// 转成java的属性名
					String feildName = toJavaName(columnName);
					// 根据列名获取类的属性对象
					Field f = cls.getDeclaredField(feildName);
					// 开启访问全选 private属性 可以访问
					f.setAccessible(true);
					// 报错！！  浮点数， java.sql.date timetamp
					Object value = null;
					if(f.getType().equals(Integer.class)) {
						value = rs.getInt(i+1);
					} else if( f.getType().equals(Long.class)) {
						value = rs.getLong(i+1);
					} else if( f.getType().equals(String.class)) {
						value = rs.getString(i+1);
					} else if (f.getType().equals(Double.class)) {
						value = rs.getDouble(i + 1);
					} else if (f.getType().equals(Float.class)) {
						value = rs.getFloat(i + 1);
					} else if (f.getType().equals(Short.class)) {
						value = rs.getShort(i + 1);
					} else if (f.getType().equals(Timestamp.class)) {
						value = rs.getTimestamp(i + 1);
					} else {
						value = rs.getObject(i+1);
					}
					
					f.set(t, value);
				}
				ret.add(t);
			}
			return ret;
		} catch (Exception e) {
			throw new RuntimeException("sql执行失败", e);
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				throw new RuntimeException("连接关闭失败", e);
			}
		}
	}
	
	/**
	 * 将数据库字段的名称转成 java 驼峰命名方式
	 * 	例如： user_name ==> userName
	 * @param columnName
	 * @return
	 */
	private static String toJavaName(String columnName) {
		// 先将字符串全部转成小写
		columnName = columnName.toLowerCase();
		// 判断是否包含下划线
		while(columnName.contains("_")) {
			// 查找下划线
			int i = columnName.indexOf("_");
			// 获取下划线后面的字符
			char c = columnName.charAt(i+1);
			// 转成大写
			c = Character.toUpperCase(c);
			// 替换小写字符
			columnName = columnName.substring(0,i) + c + columnName.substring(i+2);
		}
		return columnName;
	}

	/**
	 * 只查询一个记录，返回一个对象， 
	 * 	例如： 根据主键查询 select * from category where cid = 1
	 * @param <T>
	 * @param sql
	 * @param cls
	 * @param id
	 * @return
	 */
	public static <T> T queryById(String sql, Class<T> cls, Object id) {
		//List<T> list = query(sql, cls, id);
		//return list.isEmpty()? null : list.get(0);
		/**
		 *  1. query 查询胡数据大小没有限制
		 *  2. 多于一条的记录，应该要报错
		 */
		System.out.println("SQL："+ sql);
		System.out.println("参数：" + id);
		Connection conn = null;
		try {
			conn = openConnection();
			// 获取连接
			PreparedStatement ps = conn.prepareStatement(sql);
			// 设置参数
			ps.setObject(1, id);
			// 执行语句
			ResultSet rs = ps.executeQuery();
			T ret = null;
			// 元数据对象
			ResultSetMetaData rsmd = rs.getMetaData();
			if(rs.next()) {
				// Java 黑科技 —— 反射
				ret = cls.newInstance(); // 创建对象 ==》 new XXX(); JavaBean
				// 遍历所有的的字段
				for (int i = 0; i < rsmd.getColumnCount(); i++) {
					// 获取字段名
					String columnName = rsmd.getColumnName(i+1);
					// 转成java的属性名
					String feildName = toJavaName(columnName);
					// 根据列名获取类的属性对象
					Field f = cls.getDeclaredField(feildName);
					// 开启访问全选 private属性 可以访问
					f.setAccessible(true);
					// 报错！！
					Object value = null;
					if(f.getType().equals(Integer.class)) {
						value = rs.getInt(i+1);
					} else if( f.getType().equals(Long.class)) {
						value = rs.getLong(i+1);
					} else if( f.getType().equals(String.class)) {
						value = rs.getString(i+1);
					
					/**
					 * 其他数据类型请自行完成
					 */
					} else {
						value = rs.getObject(i+1);
					}
					
					f.set(ret, value);
				}
				// 取出改行数据的值
				if(rs.next()) {
					throw new RuntimeException("结果集大于1！");
				} else {
					return ret;
				}
			} else {
				throw new RuntimeException("没有该数据！");
			}
		} catch (Exception e) {
			throw new RuntimeException("sql执行失败", e);
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				throw new RuntimeException("连接关闭失败", e);
			}
		}
	}
	
	/**
	 * 只查询一个值
	 *例如：根据统计查询 select count(*)  from  category
	 *多于一条代码应该报错
	 */
	public static Object queryValue (String sql, Object...params){
		System.out.println("sql:" + sql);
		System.out.println("参数" + java.util.Arrays.toString(params));
		Connection conn = null;
		Object ret = null;
		try {
			//获取连接
			conn = openConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			System.out.println(ps);
			//设置参数
			for (int i = 0; i < params.length; i++) {
				
				ps.setObject(i+1, params[i]);
			}
			//执行语句
			ResultSet rs = ps.executeQuery();
			
			//元数据对象
			ResultSetMetaData rsmd = rs.getMetaData();
			
			if(rs.next()) {
				//遍历所有的字段
				if(rsmd.getColumnCount()==1) {
					//根据列名获取类的属性对象，引入反射包
					//获取字段名
					String columnName = rsmd.getColumnName(1);
					//转成Java的属性名
					String feildName = toJavaName(columnName);
					ret = rs.getObject(1);
				}else {
					throw new RuntimeException("查询不是单值");
				}
				if(rs.next()) {
					throw new RuntimeException("查询不是单值");
				}
			}
		} catch (Exception e) {
			throw new RuntimeException("sql执行失败",e);
		}finally {
			try {
				conn.close();
			} catch (SQLException e) {
				throw new RuntimeException("连接关闭失败",e);
			}
		}
		
		return ret;
	}

	public static void main(String[] args) throws SQLException {
		System.out.println(openConnection());

		String sql = "select * from category where cid > ?";

		List<Category> list = query(sql, Category.class, 5);

		System.out.println(list);

	}

	public static int update(String sql, Object...params) {
		System.out.println("SQL："+ sql);
		System.out.println("参数：" + java.util.Arrays.toString(params));
		Connection conn = null;
		try {
			conn = openConnection();
			// 获取连接
			PreparedStatement ps = conn.prepareStatement(sql);
			// 设置参数
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i + 1, params[i]);
			}
			return ps.executeUpdate();
		} catch (Exception e) {
			throw new RuntimeException("sql执行失败", e);
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				throw new RuntimeException("连接关闭失败", e);
			}
		}
	}

}
